package com.a01_2021.thousandstimes.utils;

import com.a01_2021.thousandstimes.general.entity.SysMenu;
import com.a01_2021.thousandstimes.general.entity.SysRole;
import com.a01_2021.thousandstimes.general.entity.SysUser;
import com.a01_2021.thousandstimes.general.entity.SysUserRole;
import com.a01_2021.thousandstimes.general.mapper.*;
import com.a01_2021.thousandstimes.general.service.SysMenuService;
import com.a01_2021.thousandstimes.saas.mapper.SysInstanceMapper;
import com.baomidou.dynamic.datasource.DynamicRoutingDataSource;
import com.baomidou.dynamic.datasource.annotation.DS;
import com.baomidou.dynamic.datasource.creator.DataSourceCreator;
import com.baomidou.dynamic.datasource.spring.boot.autoconfigure.DataSourceProperty;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.security.crypto.password.PasswordEncoder;
import org.springframework.stereotype.Component;

import javax.annotation.Resource;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 * @Author E1xP@foxmail.com
 * @Version 1.0
 * @PACKAGE_NAME com.a01_2021.thousandstimes.utils
 * @CLASS_NAME SqlUtils
 * @Description TODO
 * @Date 2021/2/26 下午 9:02
 **/
@Component
public class SqlUtils {

    @Value("${spring.datasource.username}")
    private String USERNAME;    //数据库用户名
    @Value("${spring.datasource.password}")
    private String PASSWORD;    //数据库密码
    @Value("${spring.datasource.url}")
    private String HEADURL;     //数据库URL
    @Value("${spring.datasource.driver-class-name}")
    private String DRIVER;      //数据库驱动类

    @Resource
    private SysInstanceMapper sysInstanceMapper;

    @Resource
    private DynamicRoutingDataSource dynamicRoutingDataSource;

    @Resource
    private DataSourceCreator dataSourceCreator;

    @Resource
    private SysUserMapper sysUserMapper;

    @Resource
    private SysRoleMapper sysRoleMapper;

    @Resource
    private SysUserRoleMapper sysUserRoleMapper;

    @Resource
    private SysMenuMapper sysMenuMapper;

    @Resource
    private SysRoleMenuMapper sysRoleMenuMapper;

    @Resource
    private SysMenuService sysMenuService;

    @Resource
    private PasswordEncoder passwordEncoder;//文本加密器

    //员工权限组
    private final List<Long> workerMenu = new ArrayList<>() {
        {
            add(10L);
            add(15L);
            add(21L);
            add(35L);
            add(36L);
            add(37L);
            add(38L);
            add(63L);
            add(64L);
            add(70L);
            add(71L);
            add(72L);
            add(80L);
            add(81L);
            add(85L);
            add(86L);
            add(90L);
            add(95L);
            add(100L);
            add(105L);
            add(106L);
            add(107L);
            add(110L);
            add(111L);
            add(112L);
            add(113L);
            add(120L);
            add(121L);
            add(130L);
            add(135L);
            add(136L);
            add(138L);
            add(140L);
            add(145L);
            add(146L);
            add(150L);
            add(151L);
            add(153L);
            add(154L);
        }
    };

    /**
     * 建立新数据库
     *
     * @param instanceName 数据库名
     */
    public void addDatabase(String instanceName) throws ClassNotFoundException, SQLException {
        Class.forName(DRIVER);
        Connection connection = DriverManager.getConnection(HEADURL, USERNAME, PASSWORD);
        Statement statement = connection.createStatement();
        statement.execute("CREATE DATABASE " + instanceName);
        statement.close();
        connection.close();
    }

    /**
     * 向系统数据源添加新数据库
     *
     * @param instanceId   新实例id
     * @param instanceName 新实例名
     */
    public void addDataSource(long instanceId, String instanceName) {
        DataSourceProperty dataSourceProperty = new DataSourceProperty();
        //设置新数据源
        dataSourceProperty.setUrl(HEADURL + instanceName);
        dataSourceProperty.setDriverClassName(DRIVER);
        dataSourceProperty.setUsername(USERNAME);
        dataSourceProperty.setPassword(PASSWORD);
        //将数据源添加到动态数据源
        DataSource dataSource = dataSourceCreator.createDataSource(dataSourceProperty);
        dynamicRoutingDataSource.addDataSource(String.valueOf(instanceId), dataSource);
    }

    /**
     * 初始化实例数据库
     *
     * @param instanceName 实例名
     * @throws ClassNotFoundException 找不到驱动类异常
     * @throws SQLException           SQL语句执行异常
     */
    public void initInstance(String instanceName) throws ClassNotFoundException, SQLException {
        String sql = "SET NAMES utf8mb4;\n" +
                "SET FOREIGN_KEY_CHECKS = 0;\n" +
                "\n" +
                "-- ----------------------------\n" +
                "-- Table structure for ability_model\n" +
                "-- ----------------------------\n" +
                "DROP TABLE IF EXISTS `ability_model`;\n" +
                "CREATE TABLE `ability_model`  (\n" +
                "  `id` bigint NOT NULL AUTO_INCREMENT,\n" +
                "  `post_id` int NULL DEFAULT NULL,\n" +
                "  `position_id` bigint NULL DEFAULT NULL,\n" +
                "  `ability1_weight` double NOT NULL,\n" +
                "  `ability2_weight` double NOT NULL,\n" +
                "  `ability3_weight` double NOT NULL,\n" +
                "  `ability4_weight` double NOT NULL,\n" +
                "  `ability5_weight` double NOT NULL,\n" +
                "  `ability6_weight` double NOT NULL,\n" +
                "  `ability7_weight` double NOT NULL,\n" +
                "  `ability8_weight` double NOT NULL,\n" +
                "  PRIMARY KEY (`id`) USING BTREE\n" +
                ") ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;\n" +
                "\n" +
                "-- ----------------------------\n" +
                "-- Table structure for attendance\n" +
                "-- ----------------------------\n" +
                "DROP TABLE IF EXISTS `attendance`;\n" +
                "CREATE TABLE `attendance`  (\n" +
                "  `id` bigint NOT NULL AUTO_INCREMENT,\n" +
                "  `user_id` bigint NOT NULL,\n" +
                "  `time` datetime(0) NOT NULL,\n" +
                "  `type` int NOT NULL,\n" +
                "  PRIMARY KEY (`id`) USING BTREE,\n" +
                "  INDEX `user_id_attendance`(`user_id`) USING BTREE,\n" +
                "  CONSTRAINT `user_id_attendance` FOREIGN KEY (`user_id`) REFERENCES `sys_user` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT\n" +
                ") ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;\n" +
                "\n" +
                "-- ----------------------------\n" +
                "-- Table structure for attendance_set\n" +
                "-- ----------------------------\n" +
                "DROP TABLE IF EXISTS `attendance_set`;\n" +
                "CREATE TABLE `attendance_set`  (\n" +
                "  `id` bigint NOT NULL AUTO_INCREMENT,\n" +
                "  `user_id` bigint NOT NULL,\n" +
                "  `start_time` datetime(0) NOT NULL,\n" +
                "  `end_time` datetime(0) NOT NULL,\n" +
                "  PRIMARY KEY (`id`) USING BTREE,\n" +
                "  INDEX `user_id_attendance_set`(`user_id`) USING BTREE,\n" +
                "  CONSTRAINT `user_id_attendance_set` FOREIGN KEY (`user_id`) REFERENCES `attendance` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT\n" +
                ") ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;\n" +
                "\n" +
                "-- ----------------------------\n" +
                "-- Table structure for communication\n" +
                "-- ----------------------------\n" +
                "DROP TABLE IF EXISTS `communication`;\n" +
                "CREATE TABLE `communication`  (\n" +
                "  `id` bigint NOT NULL AUTO_INCREMENT,\n" +
                "  `user_id` bigint NOT NULL,\n" +
                "  `to_user_id` bigint NOT NULL,\n" +
                "  `content` varchar(150) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,\n" +
                "  `send_date` datetime(0) NOT NULL,\n" +
                "  PRIMARY KEY (`id`) USING BTREE\n" +
                ") ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;\n" +
                "\n" +
                "-- ----------------------------\n" +
                "-- Table structure for dimission\n" +
                "-- ----------------------------\n" +
                "DROP TABLE IF EXISTS `dimission`;\n" +
                "CREATE TABLE `dimission`  (\n" +
                "  `id` bigint NOT NULL AUTO_INCREMENT,\n" +
                "  `user_id` bigint NOT NULL,\n" +
                "  `stats` varchar(15) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,\n" +
                "  `date` datetime(0) NULL DEFAULT NULL,\n" +
                "  `process_date` datetime(0) NULL DEFAULT NULL,\n" +
                "  `hr_comment` longtext CHARACTER SET utf8 COLLATE utf8_general_ci NULL,\n" +
                "  `reason` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,\n" +
                "  PRIMARY KEY (`id`) USING BTREE,\n" +
                "  INDEX `dimission_user_id`(`user_id`) USING BTREE,\n" +
                "  CONSTRAINT `dimission_user_id` FOREIGN KEY (`user_id`) REFERENCES `sys_user` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT\n" +
                ") ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;\n" +
                "\n" +
                "-- ----------------------------\n" +
                "-- Table structure for english_computer\n" +
                "-- ----------------------------\n" +
                "DROP TABLE IF EXISTS `english_computer`;\n" +
                "CREATE TABLE `english_computer`  (\n" +
                "  `id` int NOT NULL AUTO_INCREMENT,\n" +
                "  `user_id` bigint NOT NULL,\n" +
                "  `english_id` int NOT NULL,\n" +
                "  `computer_id` int NOT NULL,\n" +
                "  PRIMARY KEY (`id`) USING BTREE\n" +
                ") ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;\n" +
                "\n" +
                "-- ----------------------------\n" +
                "-- Table structure for experience\n" +
                "-- ----------------------------\n" +
                "DROP TABLE IF EXISTS `experience`;\n" +
                "CREATE TABLE `experience`  (\n" +
                "  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键id',\n" +
                "  `user_id` bigint NOT NULL COMMENT '用户id',\n" +
                "  `evaluator` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '评价人(针对不在系统中的',\n" +
                "  `comment` longtext CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT 'hr评价(针对不在系统中的',\n" +
                "  `company` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '公司名(针对不在系统中的',\n" +
                "  `start_date` date NOT NULL COMMENT '开始时间',\n" +
                "  `end_date` date NULL DEFAULT NULL COMMENT '结束时间',\n" +
                "  `post_id` int NULL DEFAULT NULL COMMENT '职位id',\n" +
                "  `position_id` bigint NULL DEFAULT NULL,\n" +
                "  `title` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '职位名称',\n" +
                "  `instance_id` bigint NULL DEFAULT NULL COMMENT '实例id',\n" +
                "  `score` double(16, 2) NULL DEFAULT NULL,\n" +
                "  PRIMARY KEY (`id`) USING BTREE,\n" +
                "  INDEX `user_id_experience`(`user_id`) USING BTREE,\n" +
                "  INDEX `title_experience`(`title`) USING BTREE,\n" +
                "  INDEX `positon_id_experience`(`position_id`) USING BTREE,\n" +
                "  CONSTRAINT `positon_id_experience` FOREIGN KEY (`position_id`) REFERENCES `position` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,\n" +
                "  CONSTRAINT `title_experience` FOREIGN KEY (`title`) REFERENCES `position` (`name`) ON DELETE CASCADE ON UPDATE CASCADE,\n" +
                "  CONSTRAINT `user_id_experience` FOREIGN KEY (`user_id`) REFERENCES `sys_user` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT\n" +
                ") ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;\n" +
                "\n" +
                "-- ----------------------------\n" +
                "-- Table structure for experience_detail\n" +
                "-- ----------------------------\n" +
                "DROP TABLE IF EXISTS `experience_detail`;\n" +
                "CREATE TABLE `experience_detail`  (\n" +
                "  `id` bigint NOT NULL AUTO_INCREMENT,\n" +
                "  `user_id` bigint NOT NULL,\n" +
                "  `experience_id` bigint NOT NULL,\n" +
                "  `ability_id` int NOT NULL,\n" +
                "  `score` double(16, 2) NOT NULL,\n" +
                "  `time` date NOT NULL,\n" +
                "  PRIMARY KEY (`id`) USING BTREE,\n" +
                "  INDEX `experience_id_user_id`(`experience_id`) USING BTREE,\n" +
                "  INDEX `experience_id`(`user_id`) USING BTREE,\n" +
                "  CONSTRAINT `experience_detail_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `sys_user` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,\n" +
                "  CONSTRAINT `experience_detail_ibfk_2` FOREIGN KEY (`experience_id`) REFERENCES `experience` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT\n" +
                ") ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;\n" +
                "\n" +
                "-- ----------------------------\n" +
                "-- Table structure for interpolate\n" +
                "-- ----------------------------\n" +
                "DROP TABLE IF EXISTS `interpolate`;\n" +
                "CREATE TABLE `interpolate`  (\n" +
                "  `id` bigint NOT NULL AUTO_INCREMENT,\n" +
                "  `user_id` bigint NOT NULL,\n" +
                "  `introduction` longtext CHARACTER SET utf8 COLLATE utf8_general_ci NULL,\n" +
                "  PRIMARY KEY (`id`) USING BTREE,\n" +
                "  INDEX `interpolat_user_id`(`user_id`) USING BTREE,\n" +
                "  CONSTRAINT `interpolat_user_id` FOREIGN KEY (`user_id`) REFERENCES `sys_user` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT\n" +
                ") ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;\n" +
                "\n" +
                "-- ----------------------------\n" +
                "-- Table structure for interpolate_attachment\n" +
                "-- ----------------------------\n" +
                "DROP TABLE IF EXISTS `interpolate_attachment`;\n" +
                "CREATE TABLE `interpolate_attachment`  (\n" +
                "  `id` bigint NOT NULL AUTO_INCREMENT,\n" +
                "  `interpolate_id` bigint NOT NULL,\n" +
                "  `url` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,\n" +
                "  PRIMARY KEY (`id`) USING BTREE\n" +
                ") ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;\n" +
                "\n" +
                "-- ----------------------------\n" +
                "-- Table structure for learn_today\n" +
                "-- ----------------------------\n" +
                "DROP TABLE IF EXISTS `learn_today`;\n" +
                "CREATE TABLE `learn_today`  (\n" +
                "  `id` bigint NOT NULL AUTO_INCREMENT,\n" +
                "  `user_id` bigint NOT NULL,\n" +
                "  `content` longtext CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,\n" +
                "  `log_time` datetime(0) NOT NULL,\n" +
                "  PRIMARY KEY (`id`) USING BTREE,\n" +
                "  INDEX `learn_today_user_id`(`user_id`) USING BTREE,\n" +
                "  INDEX `log_time`(`log_time`) USING BTREE,\n" +
                "  CONSTRAINT `learn_today_user_id` FOREIGN KEY (`user_id`) REFERENCES `sys_user` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT\n" +
                ") ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;\n" +
                "\n" +
                "-- ----------------------------\n" +
                "-- Table structure for note\n" +
                "-- ----------------------------\n" +
                "DROP TABLE IF EXISTS `note`;\n" +
                "CREATE TABLE `note`  (\n" +
                "  `id` bigint NOT NULL AUTO_INCREMENT,\n" +
                "  `user_id` bigint NOT NULL,\n" +
                "  `noteName` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,\n" +
                "  `noteContent` longtext CHARACTER SET utf8 COLLATE utf8_general_ci NULL,\n" +
                "  `noteDate` datetime(0) NULL DEFAULT NULL,\n" +
                "  PRIMARY KEY (`id`) USING BTREE,\n" +
                "  INDEX `note_user_id1`(`user_id`) USING BTREE,\n" +
                "  CONSTRAINT `note_user_id1` FOREIGN KEY (`user_id`) REFERENCES `sys_user` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT\n" +
                ") ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;\n" +
                "\n" +
                "-- ----------------------------\n" +
                "-- Table structure for off_work\n" +
                "-- ----------------------------\n" +
                "DROP TABLE IF EXISTS `off_work`;\n" +
                "CREATE TABLE `off_work`  (\n" +
                "  `id` bigint NOT NULL AUTO_INCREMENT,\n" +
                "  `user_id` bigint NOT NULL,\n" +
                "  `date` datetime(0) NOT NULL,\n" +
                "  `off_work_date` date NOT NULL,\n" +
                "  `type` int NOT NULL,\n" +
                "  `reason` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,\n" +
                "  `stats` int NOT NULL,\n" +
                "  `handler` bigint NULL DEFAULT NULL,\n" +
                "  PRIMARY KEY (`id`) USING BTREE\n" +
                ") ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;\n" +
                "\n" +
                "-- ----------------------------\n" +
                "-- Table structure for personnel_infomation\n" +
                "-- ----------------------------\n" +
                "DROP TABLE IF EXISTS `personnel_infomation`;\n" +
                "CREATE TABLE `personnel_infomation`  (\n" +
                "  `id` bigint NOT NULL AUTO_INCREMENT,\n" +
                "  `name` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,\n" +
                "  `user_id` bigint NOT NULL,\n" +
                "  `personal_id` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,\n" +
                "  `gender` varchar(5) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,\n" +
                "  `birthday` date NULL DEFAULT NULL,\n" +
                "  `wechat` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,\n" +
                "  `qq` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,\n" +
                "  `resume` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,\n" +
                "  PRIMARY KEY (`id`) USING BTREE,\n" +
                "  INDEX `userId`(`user_id`) USING BTREE,\n" +
                "  CONSTRAINT `personnel_infomation_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `sys_user` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT\n" +
                ") ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;\n" +
                "\n" +
                "-- ----------------------------\n" +
                "-- Table structure for position\n" +
                "-- ----------------------------\n" +
                "DROP TABLE IF EXISTS `position`;\n" +
                "CREATE TABLE `position`  (\n" +
                "  `id` bigint NOT NULL AUTO_INCREMENT,\n" +
                "  `post_id` int NOT NULL,\n" +
                "  `name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,\n" +
                "  `description` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,\n" +
                "  PRIMARY KEY (`id`) USING BTREE,\n" +
                "  INDEX `name`(`name`) USING BTREE\n" +
                ") ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;\n" +
                "\n" +
                "-- ----------------------------\n" +
                "-- Table structure for recruit\n" +
                "-- ----------------------------\n" +
                "DROP TABLE IF EXISTS `recruit`;\n" +
                "CREATE TABLE `recruit`  (\n" +
                "  `id` bigint NOT NULL AUTO_INCREMENT,\n" +
                "  `sponseor_id` bigint NOT NULL,\n" +
                "  `examiner_id` bigint NOT NULL,\n" +
                "  `status` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,\n" +
                "  PRIMARY KEY (`id`) USING BTREE,\n" +
                "  INDEX `sponseor_id_recruit`(`sponseor_id`) USING BTREE,\n" +
                "  INDEX `examiner_id_recruit`(`examiner_id`) USING BTREE,\n" +
                "  CONSTRAINT `examiner_id_recruit` FOREIGN KEY (`examiner_id`) REFERENCES `sys_user` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,\n" +
                "  CONSTRAINT `sponseor_id_recruit` FOREIGN KEY (`sponseor_id`) REFERENCES `sys_user` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT\n" +
                ") ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;\n" +
                "\n" +
                "-- ----------------------------\n" +
                "-- Table structure for recruit_info\n" +
                "-- ----------------------------\n" +
                "DROP TABLE IF EXISTS `recruit_info`;\n" +
                "CREATE TABLE `recruit_info`  (\n" +
                "  `id` bigint NOT NULL AUTO_INCREMENT,\n" +
                "  `position_id` bigint NOT NULL,\n" +
                "  `hr` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,\n" +
                "  `city` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,\n" +
                "  `company` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,\n" +
                "  `salary` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,\n" +
                "  `requirement` longtext CHARACTER SET utf8 COLLATE utf8_general_ci NULL,\n" +
                "  `start_time` datetime(0) NULL DEFAULT NULL,\n" +
                "  `number` int NULL DEFAULT NULL,\n" +
                "  PRIMARY KEY (`id`) USING BTREE,\n" +
                "  INDEX `positon_id_position`(`position_id`) USING BTREE,\n" +
                "  CONSTRAINT `positon_id_position` FOREIGN KEY (`position_id`) REFERENCES `position` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT\n" +
                ") ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;\n" +
                "\n" +
                "-- ----------------------------\n" +
                "-- Table structure for report\n" +
                "-- ----------------------------\n" +
                "DROP TABLE IF EXISTS `report`;\n" +
                "CREATE TABLE `report`  (\n" +
                "  `id` bigint NOT NULL AUTO_INCREMENT,\n" +
                "  `user_id` bigint NOT NULL,\n" +
                "  `reportDescription` longtext CHARACTER SET utf8 COLLATE utf8_general_ci NULL,\n" +
                "  `reportTheme` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,\n" +
                "  `reportContent` longtext CHARACTER SET utf8 COLLATE utf8_general_ci NULL,\n" +
                "  `date` datetime(0) NOT NULL ON UPDATE CURRENT_TIMESTAMP(0),\n" +
                "  PRIMARY KEY (`id`) USING BTREE,\n" +
                "  INDEX `report_user_id`(`user_id`) USING BTREE,\n" +
                "  INDEX `report_id`(`id`) USING BTREE,\n" +
                "  CONSTRAINT `report_user_id` FOREIGN KEY (`user_id`) REFERENCES `sys_user` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT\n" +
                ") ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;\n" +
                "\n" +
                "-- ----------------------------\n" +
                "-- Table structure for report_attachment\n" +
                "-- ----------------------------\n" +
                "DROP TABLE IF EXISTS `report_attachment`;\n" +
                "CREATE TABLE `report_attachment`  (\n" +
                "  `report_id` bigint NOT NULL,\n" +
                "  `attachment_url` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,\n" +
                "  INDEX `report_id`(`report_id`) USING BTREE,\n" +
                "  CONSTRAINT `report_attachment_report_id` FOREIGN KEY (`report_id`) REFERENCES `report` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT\n" +
                ") ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;\n" +
                "\n" +
                "-- ----------------------------\n" +
                "-- Table structure for sys_menu\n" +
                "-- ----------------------------\n" +
                "DROP TABLE IF EXISTS `sys_menu`;\n" +
                "CREATE TABLE `sys_menu`  (\n" +
                "  `id` bigint NOT NULL AUTO_INCREMENT,\n" +
                "  `name` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,\n" +
                "  `url` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,\n" +
                "  `des` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,\n" +
                "  PRIMARY KEY (`id`) USING BTREE\n" +
                ") ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;\n" +
                "\n" +
                "-- ----------------------------\n" +
                "-- Table structure for sys_role\n" +
                "-- ----------------------------\n" +
                "DROP TABLE IF EXISTS `sys_role`;\n" +
                "CREATE TABLE `sys_role`  (\n" +
                "  `id` bigint NOT NULL AUTO_INCREMENT,\n" +
                "  `name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,\n" +
                "  `des` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,\n" +
                "  PRIMARY KEY (`id`) USING BTREE,\n" +
                "  INDEX `user_id`(`id`) USING BTREE\n" +
                ") ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;\n" +
                "\n" +
                "-- ----------------------------\n" +
                "-- Table structure for sys_role_menu\n" +
                "-- ----------------------------\n" +
                "DROP TABLE IF EXISTS `sys_role_menu`;\n" +
                "CREATE TABLE `sys_role_menu`  (\n" +
                "  `role_id` bigint NOT NULL,\n" +
                "  `menu_id` bigint NOT NULL,\n" +
                "  INDEX `sys_role_menu_role_id`(`role_id`) USING BTREE,\n" +
                "  INDEX `sys_role_menu_menu_id`(`menu_id`) USING BTREE,\n" +
                "  CONSTRAINT `sys_role_menu_menu_id` FOREIGN KEY (`menu_id`) REFERENCES `sys_menu` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,\n" +
                "  CONSTRAINT `sys_role_menu_role_id` FOREIGN KEY (`role_id`) REFERENCES `sys_role` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT\n" +
                ") ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;\n" +
                "\n" +
                "-- ----------------------------\n" +
                "-- Table structure for sys_user\n" +
                "-- ----------------------------\n" +
                "DROP TABLE IF EXISTS `sys_user`;\n" +
                "CREATE TABLE `sys_user`  (\n" +
                "  `id` bigint NOT NULL AUTO_INCREMENT,\n" +
                "  `username` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,\n" +
                "  `password` varchar(60) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,\n" +
                "  `head_pic` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,\n" +
                "  `user_type` int NULL DEFAULT NULL,\n" +
                "  PRIMARY KEY (`id`) USING BTREE\n" +
                ") ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;\n" +
                "\n" +
                "-- ----------------------------\n" +
                "-- Table structure for sys_user_role\n" +
                "-- ----------------------------\n" +
                "DROP TABLE IF EXISTS `sys_user_role`;\n" +
                "CREATE TABLE `sys_user_role`  (\n" +
                "  `id` bigint NOT NULL AUTO_INCREMENT,\n" +
                "  `user_id` bigint NOT NULL,\n" +
                "  `role_id` bigint NOT NULL,\n" +
                "  PRIMARY KEY (`id`) USING BTREE,\n" +
                "  INDEX `user_id1`(`user_id`) USING BTREE,\n" +
                "  INDEX `role_id1`(`role_id`) USING BTREE,\n" +
                "  CONSTRAINT `sys_user_role_ibfk_1` FOREIGN KEY (`role_id`) REFERENCES `sys_role` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,\n" +
                "  CONSTRAINT `sys_user_role_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `sys_user` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT\n" +
                ") ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;\n" +
                "\n" +
                "-- ----------------------------\n" +
                "-- Table structure for vote\n" +
                "-- ----------------------------\n" +
                "DROP TABLE IF EXISTS `vote`;\n" +
                "CREATE TABLE `vote`  (\n" +
                "  `id` bigint NOT NULL AUTO_INCREMENT,\n" +
                "  `be_vote_user_id` bigint NOT NULL,\n" +
                "  `vote_user_id` bigint NOT NULL,\n" +
                "  `score` double(16, 2) NULL DEFAULT NULL,\n" +
                "  `vote_time` date NOT NULL,\n" +
                "  `name` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,\n" +
                "  PRIMARY KEY (`id`) USING BTREE,\n" +
                "  INDEX `be_vote_user_id`(`be_vote_user_id`) USING BTREE,\n" +
                "  INDEX `vote_user_id`(`vote_user_id`) USING BTREE,\n" +
                "  INDEX `date`(`vote_time`) USING BTREE,\n" +
                "  CONSTRAINT `be_vote_user_id` FOREIGN KEY (`be_vote_user_id`) REFERENCES `sys_user` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,\n" +
                "  CONSTRAINT `vote_user_id` FOREIGN KEY (`vote_user_id`) REFERENCES `sys_user` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT\n" +
                ") ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;\n" +
                "\n" +
                "-- ----------------------------\n" +
                "-- Table structure for work_log\n" +
                "-- ----------------------------\n" +
                "DROP TABLE IF EXISTS `work_log`;\n" +
                "CREATE TABLE `work_log`  (\n" +
                "  `id` bigint NOT NULL AUTO_INCREMENT,\n" +
                "  `user_id` bigint NOT NULL,\n" +
                "  `isComplete` bit(1) NULL DEFAULT NULL,\n" +
                "  `workPlan` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,\n" +
                "  `workRecord` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,\n" +
                "  `workSummary` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,\n" +
                "  `date` datetime(0) NULL DEFAULT NULL,\n" +
                "  PRIMARY KEY (`id`) USING BTREE,\n" +
                "  INDEX `note_user_id`(`user_id`) USING BTREE,\n" +
                "  CONSTRAINT `note_user_id` FOREIGN KEY (`user_id`) REFERENCES `sys_user` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT\n" +
                ") ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;\n" +
                "\n" +
                "SET FOREIGN_KEY_CHECKS = 1;";
        Class.forName(DRIVER);
        Connection connection = DriverManager.getConnection(HEADURL + instanceName + "?allowMultiQueries=true", USERNAME, PASSWORD);
        Statement statement = connection.createStatement();
        statement.execute(sql);
        statement.close();
        connection.close();
    }

    /**
     * 用于初始化数据库数据
     *
     * @param instanceid   实例id
     * @param tempPassword 临时密码
     */
    @DS("#instanceid")
    public void initDatabase(String instanceid, String tempPassword) {
        //添加临时管理员用户
        SysUser sysUser = new SysUser();
        sysUser.setUsername("admin");
        sysUser.setPassword(passwordEncoder.encode(tempPassword));
        sysUser.setUserType(2);
        sysUserMapper.insert(sysUser);
        //添加角色
        SysRole admin = new SysRole();
        admin.setName("admin");
        admin.setDes("系统管理员（HR");
        sysRoleMapper.insert(admin);
        SysRole worker = new SysRole();
        worker.setName("员工");
        worker.setDes("普通员工");
        sysRoleMapper.insert(worker);
        //添加角色
        SysUserRole sysUserRole = new SysUserRole();
        sysUserRole.setUserId(sysUser.getId());
        sysUserRole.setRoleId(admin.getId());
        sysUserRoleMapper.insert(sysUserRole);
        //初始化权限列表
        List<SysMenu> sysMenuList = sysMenuService.getMenuFromMaster();
        sysMenuMapper.insertMenu(sysMenuList);
        //初始化权限角色
        List<Long> allMenu = sysMenuMapper.getAllMenu();
        sysRoleMenuMapper.insertRoleMenu(admin.getId(), allMenu);//为admin添加所有权限
        sysRoleMenuMapper.insertRoleMenu(worker.getId(), workerMenu);//添加员工权限
    }
}
